/**
 * Class:   VolunteerActivityManager
 * 
 * Methods:
 * 
 *      public String insertActivity( VolunteerActivity activity );
 *      
 *      public ArrayList<VolunteerActivity> searchActivity( VolunteerActivity activity );
 *      
 *      public ArrayList<VolunteerActivity> generateActivityList
 *      		( VolunteerActivity activity, Date date1, Date date2 );
 *      
 *      public String updateActivity( VolunteerActivity original,
 *      							  VolunteerActivity changed );
 *      
 *      public int getActivityID( VolunteerActivity activity );
 *      
 *      private boolean lackingRequirement( VolunteerActivity activity );
 * 
 * Remarks:
 * 
 * 		This class can be touched by the controllers.
 * 
 * @author ( Sharmaine Lim )
 * @version ( 2013-02-28 // YYYY-MM-DD )
 */

package managers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import models.Date;
import models.Organization;
import models.Participant;
import models.VolunteerActivity;

public class VolunteerActivityManager extends DatabaseManager {
	
    /**
     * Constructors
     */
    
    public VolunteerActivityManager() {}
    
    
    
    /**
     *  Insert Activity
     *  	- inserts an activity into the volunteer activity table
     *  
     *  Parameters
     *		- VolunteerActivity activity: the activity to be added
     *	
     *	Returns
     *		- String "Lacking Requirements": requirements
     *				were not fulfilled
     *		- String "Success": activity is successfully inserted
     * 		- String "Activity": insertion failed for the activity table
     * 		- String "Activity_Organization": insertion failed for the
     * 				v_activityOrganization table
     * 		- String "Beneficiary": insertion failed for the beneficiary
     * 				table ( new beneficiaries )
     * 		- String "Activity_Beneficiary": insertion failed for the
     * 				v_activityBeneficiary table
     * 		- String "Adviser": insertion failed for the adviser table
     * 				( new advisers )
     * 		- String "Activity_Adviser": insertion failed for the
     * 				v_activityAdviser table
     * 		- String "Participant": insertion failed for the participant
     * 				table ( new participants )
     * 		- String "Activity_Participant": insertion failed for the
     * 				v_activityParticipant table
     * 		- String "Remark": insertion failed for the v_Remark table
     */
    public String insertActivity( VolunteerActivity activity ) {
    	if ( lackingRequirement( activity ) ) {
    		return "Lacking Requirements";
    	}
    	
    	try {
    		/* Declaration of Block Variables */
    		PreparedStatement preparedStatement;
    		int activityID;
    		ArrayList<Organization> organizationList;
    		ArrayList<String> beneficiaryList;
    		ArrayList<String> adviserList;
    		ArrayList<Participant> participantList;
    		ArrayList<String> remarkList;
    		
    		/* Insert activity data into `volunteerActivity` table */
    		connect();
    		
    		preparedStatement = getConnection()
    							.prepareStatement( "INSERT INTO" +
    											   " `volunteerActivity`" +
    											   " (`title`, `activityType`," +
    											   " `dateFiled`," +
    											   " `dateApproved`," +
    											   " `dateOfActivity`," +
    											   " `dateDue`, `canceled`)" +
    											   " VALUES" +
    											   " (?, ?, ?, ?, ?, ?, ?)" );

            preparedStatement.setString( 1, activity.getTitle() );
            preparedStatement.setString( 2, activity.getType() );
            
            preparedStatement.setString( 3, activity.getDateFiled()
            										.toString() );
            preparedStatement.setString( 4, activity.getDateApproved()
            										.toString() );
            preparedStatement.setString( 5, activity.getDateOfActivity()
            										.toString() );
            preparedStatement.setString( 6, activity.getDateDue()
            										.toString() );
            
            preparedStatement.setBoolean( 7, activity.isCanceled() );
            preparedStatement.executeUpdate();
            
            disconnect();
            
            activityID = getActivityID( activity );
            
            if ( activityID == 0 ) {
            	return "Activity";
            }
            
            /* Add organization links to the database */
            organizationList = activity.getOrganizations();
            
            for ( Organization organization : organizationList ) {
            	
            	/* Declaration of Block Variables */
            	int organizationID = new OrganizationManager()
            						 .getOrganizationID( organization );
            	
            	if ( organizationID != 0 ) {
            		/* Insert relation into `v_activityOrganization` table */
            		if ( !( new VolunteerActivityRelationsManager()
            			.insertActivityOrganizationRelation( activityID,
            												 organizationID ) ) ) {
            			return "Activity_Organization";
            		}
            	}
            }
            
            /* Add beneficiary links to the database */
            beneficiaryList = activity.getBeneficiaries();
            
            for ( String beneficiary : beneficiaryList ) {
            	
            	/* Declaration of Block Variables */
            	int beneficiaryID = new BeneficiaryManager()
            						.getBeneficiaryID( beneficiary );
            	
            	if ( beneficiaryID == 0 ) {
            		
            		if ( !( new BeneficiaryManager()
            				.insertBeneficiary( beneficiary ) ) ) {
            			return "Beneficiary";
            		}
            		
            		beneficiaryID = new BeneficiaryManager()
            						.getBeneficiaryID( beneficiary );
            		
            	}
        		
            	/* Insert relation into `v_activityBeneficiary` table */
            	if ( !( new VolunteerActivityRelationsManager()
            		.insertActivityBeneficiaryRelation( activityID,
            											beneficiaryID ) ) ) {
            		return "Activity_Beneficiary";
            	}
            }
            
            /* Add adviser links to the database */
            adviserList = activity.getAdvisers();
            
            for ( String adviser : adviserList ){
            	
            	/* Declaration of Block Variables */
            	int adviserID = new AdviserManager().getAdviserID( adviser );
            	
            	if ( adviserID == 0 ) {
            		
            		if ( !( new AdviserManager().insertAdviser( adviser ) ) ) {
            			return "Beneficiary";
            		}
            		
            		adviserID = new AdviserManager().getAdviserID( adviser );
            		
            	}
        		
            	/* Insert relation into `v_activityAdviser` table */
            	if ( !( new VolunteerActivityRelationsManager()
            		.insertActivityAdviserRelation( activityID, adviserID ) ) ) {
            		return "Activity_Adviser";
            	}
            }
            
            /* Add participant links to the database */
            participantList = activity.getParticipants();
            
            for ( Participant participant : participantList ){
            	
            	/* Declaration of Block Variables */
            	int participantID = new ParticipantManager()
            						.getParticipantID( participant );
            	
            	if ( participantID == 0 ) {
            		
            		if ( !( new ParticipantManager()
            				.insertParticipant( participant ) ) ) {
            			return "Participant";
            		}
            		
            		participantID = new ParticipantManager()
            						.getParticipantID( participant );
            		
            	}
        		
            	/* Insert relation into `v_activityParticipant` table */
            	if ( !( new VolunteerActivityRelationsManager()
            		.insertActivityParticipantRelation( activityID, participantID ) ) ) {
            		return "Activity_Participant";
            	}
            }
            
            /* Add remark links to the database */
            remarkList = activity.getRemarks();
            
            if ( remarkList != null ) {
	            for ( String remark : remarkList ){
	        		
	            	if ( remark.equals( "" ) )
	            		break;
	            	
	            	/* Insert remark into `v_remark` table */
	        		if ( !( new RemarkManager().insertRemark( activityID,
	        												  remark,
	        												  "Volunteer" ) ) ) {
	        			return "Remark";
	        		}
	            	
	            }
            }
            
            disconnect();
            return "Success";
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return "Activity";
    }
    
    
    
    /**
     *  Search for Activity
     *  	- generates the list of activities in the activity table based
     *  	  on the given specifications
	 * 
	 * Searchables
	 * 		- activity title
	 * 		- activity type
	 * 		- date filed
	 * 		- date approved
	 * 		- date of activity
	 * 		- date due
	 * 		- canceled
	 * 		- remarks
	 * 		- organization name
	 * 		- organization type
	 * 		- beneficiary name
	 * 		- has evaluation
     *  
     *  Parameters
     *		- VolunteerActivity activity: the activity
     *				specifications
     *		- int canceled: whether the activity has been canceled or not
     *			> 0 - search for non-canceled activities
     *			> 1 - search for canceled activities
     *			> 2 - search for canceled and non-canceled activities
     *		- int hasEvaluations: whether the activity has been
     *							  evaluated or not
     *			> 0 - search for activities with evaluations
     *			> 1 - search for activities without evaluations
     *			> 2 - search for activities with and activities without
     *				  evaluations
     *	
     *	Returns
     *		- ArrayList<VolunteerActivity>.size() == 0: no activities found
     * 		- ArrayList<VolunteerActivity>.size() > 0: list of activities found
     */
    public ArrayList<VolunteerActivity> searchActivity( VolunteerActivity activity, int canceled,
    													int hasEvaluations ) {
    	
    	/* Declaration of Local Variables */
    	ArrayList<VolunteerActivity> activityList = new ArrayList<VolunteerActivity>();
    	int notNull = 0;
    	String query = "";
		
		/* Activity Title */
		if ( activity.getTitle() != null && !activity.getTitle().equals( "" ) ) {
			query = query.concat( " `title` LIKE ?" );
			notNull++;
		}
		
		/* Activity Type */
		if ( activity.getType() != null && !activity.getType().equals( "" ) ) {
			query = query.concat( addAnd(notNull) + " `activityType` LIKE ?" );
			notNull++;
		}
		
		/* Date Filed */
		if ( activity.getDateFiled() != null ) {
			query = query.concat( addAnd(notNull) + " `dateFiled` = ?" );
			notNull++;
		}
		
		/* Date Approved */
		if ( activity.getDateApproved() != null ) {
			query = query.concat( addAnd(notNull) + " `dateApproved` = ?" );
			notNull++;
		}
		
		/* Date of Activity */
		if ( activity.getDateOfActivity() != null ) {
			query = query.concat( addAnd(notNull) + " `dateOfActivity` = ?" );
			notNull++;
		}
		
		/* Date Due */
		if ( activity.getDateDue() != null ) {
			query = query.concat( addAnd(notNull) + " `dateDue` = ?" );
			notNull++;
		}
		
		/* Canceled */
		if ( canceled == 0 || canceled == 1 ) {
			query = query.concat( addAnd(notNull) + " `canceled` = ?" );
			notNull++;
		}
		
		/* Remarks */
		if ( activity.getRemarks() != null &&
			 activity.getRemarks().size() > 0 ) {
			
			if ( !activity.getRemarks().get( 0 ).equals( "" ) ) {
				query = query.concat( addAnd(notNull) + " `activityID` IN" +
    								  " (SELECT `activityID` FROM `v_remark`" +
    								  " WHERE `remark` LIKE ?)" );
    			notNull++;
			}
		}
		
		/* Organization */
		if ( activity.getOrganizations() != null &&
			 activity.getOrganizations().size() > 0 ) {
        	
        	/* Declaration of Block Variables */
			Organization organization;
			int notNull2 = 0;
			
			query = query.concat( addAnd(notNull) +
								  " `activityID` IN (SELECT `activityID`" +
								  " FROM `v_activityOrganization` WHERE" +
								  " `organizationID` IN" +
								  " (SELECT `organizationID`" +
								  " FROM `organization` WHERE" );
			
			organization = activity.getOrganizations().get( 0 );
			
			if ( organization.getName() != null &&
				 !organization.getName().equals( "" ) ) {
				
				query = query.concat( " `organizationName` = ?" );
				notNull2++;
				notNull++;
			}
			
			if ( organization.getType() != null &&
				 !organization.getType().equals( "" ) ) {
				
				query = query.concat( addAnd( notNull2 ) +
							  " `organizationType` = ?" );
				notNull++;
			}
			
			query = query.concat( "))" );
		}
		
		/* Beneficiary */
		if ( activity.getBeneficiaries() != null &&
			 activity.getBeneficiaries().size() > 0 ) {
			
			if ( !activity.getBeneficiaries().get( 0 ).equals( "" ) ) {
				
				query = query.concat( addAnd(notNull) +
    								  " `activityID` IN" +
    								  " (SELECT `activityID`" +
    								  " FROM `v_activityBeneficiary`" +
    								  " WHERE `beneficiaryID` IN" +
    								  " (SELECT `beneficiaryID`" +
    								  " FROM `beneficiary` WHERE" +
    								  " `beneficiaryName` = ?))" );
				notNull++;
			}
		}
		
		/* Has Evaluations */
		if ( hasEvaluations == 0 ) {
			query = query.concat( addAnd(notNull) + "`activityID` IN" +
								  " (SELECT `activityID` FROM `evaluationAdviser`) AND `activityID` IN" +
								  " (SELECT `activityID` FROM `evaluationBeneficiary`) AND `activityID` IN" +
								  " (SELECT `activityID` FROM `evaluationOrganization`)" );
		}
		else if ( hasEvaluations == 1 ) {
			query = query.concat( addAnd(notNull) + "(`activityID` NOT IN" +
								  " (SELECT `activityID` FROM `evaluationAdviser`)" +
								  " OR `activityID` NOT IN" +
								  " (SELECT `activityID` FROM `evaluationBeneficiary`)" +
								  " OR `activityID` NOT IN" +
								  " (SELECT `activityID` FROM `evaluationOrganization`))" );
		}
    	
    	try {
        	/* Declaration of Block Variables */
    		int n = 1;
    		PreparedStatement preparedStatement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		if ( query.equals( "" ) ) {
    			query = "SELECT * FROM `volunteerActivity` ORDER BY `title`";
    		}
    		else {
    			query = "SELECT * FROM `volunteerActivity` WHERE".concat( query + " ORDER BY `title`" );
    		}
    		
    		preparedStatement = getConnection().prepareStatement( query );
    		
    		if ( query.contains( "`title` LIKE" ) ) {
    			preparedStatement.setString( n, "%" + activity.getTitle() + "%" );
    			n++;
    		}
    		
    		if ( query.contains( "`activityType`" ) ) {
    			preparedStatement.setString( n, "%" + activity.getType() + "%" );
    			n++;
    		}
    		
    		if ( query.contains( "`dateFiled`" ) ) {
    			preparedStatement.setString( n, activity.getDateFiled().toString() );
    			n++;
    		}
    		
    		if ( query.contains( "`dateApproved`" ) ) {
    			preparedStatement.setString( n, activity.getDateApproved().toString() );
    			n++;
    		}
    		
    		if ( query.contains( "`dateOfActivity`" ) ) {
    			preparedStatement.setString( n, activity.getDateOfActivity().toString() );
    			n++;
    		}
    		
    		if ( query.contains( "`dateDue`" ) ) {
    			preparedStatement.setString( n, activity.getDateDue().toString() );
    			n++;
    		}
    		
    		if ( query.contains( "`canceled`" ) ) {
    			preparedStatement.setInt( n, canceled );
    			n++;
    		}
    		
    		if ( query.contains( "`remark`" ) ) {
    			preparedStatement.setString( n, "%" + activity.getRemarks().get( 0 ) + "%" );
    			n++;
    		}
    		
    		if ( query.contains( "`organizationName`" ) ) {
    			preparedStatement.setString( n, activity.getOrganizations().get( 0 ).getName() );
    			n++;
    		}
    		
    		if ( query.contains( "`organizationType`" ) ) {
    			preparedStatement.setString( n, activity.getOrganizations().get( 0 ).getType() );
    			n++;
    		}
    		
    		if ( query.contains( "`beneficiaryName`" ) ) {
    			preparedStatement.setString( n, activity.getBeneficiaries().get( 0 ) );
    		}
    		
    		resultSet = preparedStatement.executeQuery();
    		
    		while ( resultSet.next() ) {
            	
            	/* Declaration of Block Variables */
    			int activityID = resultSet.getInt( "activityID" );
    			VolunteerActivity currentActivity;
    			
    			currentActivity = new VolunteerActivity
    							  ( resultSet.getString( "title" ),
    								resultSet.getString( "activityType" ),
    								new BeneficiaryManager()
    							    	.searchBeneficiary( activityID ),
    								new RemarkManager()
    							  		.searchRemark( activityID, "Volunteer" ),
    								parseDate( resultSet
    							  			   .getDate( "dateFiled" ) ),
    								parseDate( resultSet
    										   .getDate( "dateApproved" ) ),
    								parseDate( resultSet
    										   .getDate( "dateOfActivity" ) ),
    								parseDate( resultSet
    										   .getDate( "dateDue" ) ),
    								new OrganizationManager()
    							    	.searchOrganization( activityID ),
    								new AdviserManager()
    							    	.searchAdviser( activityID ),
    								new ParticipantManager()
    							    	.searchParticipant( activityID ),
    								resultSet.getBoolean( "canceled" ),
    								new OrganizationEvaluationManager()
    							  		.searchOrganizationEvaluation( activityID ),
    							  	new AdviserEvaluationManager()
    									.searchAdviserEvaluation( activityID ),
    								new BeneficiaryEvaluationManager()
    									.searchBeneficiaryEvaluation( activityID ) );
    			
    			activityList.add( currentActivity );
    		}
    		
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    	
    	return activityList;
    }
    
    
    
    /**
     *  Generate Activity List
     *  	- generates the list of activities in the activity table based
     *  	  on the given specifications
	 * 
	 * Searchables
	 * 		- activity type
	 * 		- date range of activities
	 * 		- canceled
	 * 		- organization name
	 * 		- organization type
	 * 		- beneficiary name
	 * 		- has evaluation
     *  
     *  Parameters
     *		- VolunteerActivity activity: the activity
     *				specifications
     *		- int hasEvaluation: whether the activity has been
     *							 evaluated or not
     *			> 0 - search for activities with and activities without
     *				  evaluations
     *			> 1 - search for activities with evaluations
     *			> 2 - search for activities without evaluations
     *	
     *	Returns
     *		- ArrayList<VolunteerActivity>.size() == 0: no activities found
     * 		- ArrayList<VolunteerActivity>.size() > 0: list of activities found
     */
    public ArrayList<VolunteerActivity> generateActivityList( VolunteerActivity activity,
    														  Date date1, Date date2,
    														  int hasEvaluation ) {
    	
    	/* Declaration of Local Variables */
    	ArrayList<VolunteerActivity> activityList = new ArrayList<VolunteerActivity>();
    	
    	try {
        	/* Declaration of Block Variables */
    		int notNull = 0;
    		String query;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		query = "SELECT * FROM `volunteerActivity` WHERE";
    		
    		/* Activity Type */
    		if ( activity.getType() != null && !activity.getType().equals( "" ) ) {
    			query = query.concat( addAnd(notNull) +
    								  " `activityType` LIKE \"%" +
    								  activity.getType() + "%\"" );
    			notNull++;
    		}
    		
    		/* Date of Activity */
    		if ( date1 != null && !date1.toString().equals("1990-1-0") &&
    			 date2 != null && !date2.toString().equals("1900-1-0")) {
    			query = query.concat( addAnd(notNull) +
    								  " `dateOfActivity` BETWEEN \"" +
    								  date1 + "\" AND \"" + date2 + "\"" );
    			notNull++;
    		}
    		
    		/* Canceled */
    		query = query.concat( addAnd(notNull) + " `canceled` = " );
    		
    		if ( activity.isCanceled() ) {
    			query = query.concat( "1" );
    		}
    		else {
    			query = query.concat( "0" );
    		}
			notNull++;
    		
    		/* Organization */
    		if ( activity.getOrganizations() != null &&
    			 activity.getOrganizations().size() > 0 ) {
            	
            	/* Declaration of Block Variables */
    			Organization organization;
    			int notNull2 = 0;
    			
    			query = query.concat( addAnd(notNull) +
    								  " `activityID` IN (SELECT `activityID`" +
    								  " FROM `v_activityOrganization` WHERE" +
    								  " `organizationID` IN" +
    								  " (SELECT `organizationID`" +
    								  " FROM `organization` WHERE" );
    			
    			organization = activity.getOrganizations().get( 0 );
    			
    			if ( organization.getName() != null &&
    				 !organization.getName().equals( "" ) ) {
    				
    				query.concat( " `organizationName` = \"" +
    							  organization.getName() + "\"" );
    				notNull2++;
    			}
    			
    			if ( organization.getType() != null &&
    				 !organization.getType().equals( "" ) ) {
    				
    				query.concat( addAnd( notNull2 ) +
    							  " `organizationType` = \"" +
    							  organization.getType() + "\"" );
    			}
    			
    			query = query.concat( ")" );
    			
    			notNull++;
    		}
    		
    		/* Beneficiary */
    		if ( activity.getBeneficiaries() != null &&
    			 activity.getBeneficiaries().size() > 0 ) {
    			
    			if ( !activity.getBeneficiaries().get( 0 ).equals( "" ) ) {
    				
    				query = query.concat( addAnd(notNull) +
        								  " `activityID` IN" +
        								  " (SELECT `activityID`" +
        								  " FROM `v_activityBeneficiary`" +
        								  " WHERE `beneficiaryID` IN" +
        								  " (SELECT `beneficiaryID`" +
        								  " FROM `beneficiary` WHERE" +
        								  " `beneficiaryName` = \"" +
        								  activity.getBeneficiaries().get( 0 ) +
        								  "\"" );
        			
        			notNull++;
    			}
    		}
    		
    		/* Has Evaluations */
    		if ( hasEvaluation > 0 && hasEvaluation < 3 ) {
    			if ( hasEvaluation == 1 ) {
        			query = query.concat( addAnd(notNull) + " `activityID` IN" +
        								  " (SELECT `activityID` FROM" +
        								  " `evaluationOrganization`) AND" +
        								  " `activityID` IN (SELECT" +
        								  " `activityID` FROM" +
        								  " `evaluationAdviser`) AND" +
        								  " `activityID` IN (SELECT" +
        								  " `activityID` FROM" +
        								  " `evaluationBeneficiary`)" );
    			}
    			else if ( hasEvaluation == 2 ) {
        			query = query.concat( addAnd(notNull) + " (`activityID`" +
        								  " NOT IN (SELECT `activityID` FROM" +
        								  " `evaluationOrganization`) OR" +
        								  " `activityID` NOT IN (SELECT" +
        								  " `activityID` FROM" +
        								  " `evaluationAdviser`) OR" +
        								  " `activityID` NOT IN (SELECT" +
        								  " `activityID` FROM" +
        								  " `evaluationBeneficiary`))" );
    			}
    			notNull++;
    		}
    		
    		statement = getConnection().createStatement();
    		
    		System.out.println( query );
    		
    		resultSet = statement.executeQuery( query );
    		
    		while ( resultSet.next() ) {
            	
            	/* Declaration of Block Variables */
    			int activityID = resultSet.getInt( "activityID" );
    			VolunteerActivity currentActivity;
    			
    			currentActivity = new VolunteerActivity
    							  ( resultSet.getString( "title" ),
    								resultSet.getString( "activityType" ),
    								new BeneficiaryManager()
    							    	.searchBeneficiary( activityID ),
    								new RemarkManager()
    							  		.searchRemark( activityID, "Volunteer" ),
    								parseDate( resultSet
    							  			   .getDate( "dateFiled" ) ),
    								parseDate( resultSet
    										   .getDate( "dateApproved" ) ),
    								parseDate( resultSet
    										   .getDate( "dateOfActivity" ) ),
    								parseDate( resultSet
    										   .getDate( "dateDue" ) ),
    								new OrganizationManager()
    							    	.searchOrganization( activityID ),
    								new AdviserManager()
    							    	.searchAdviser( activityID ),
    								new ParticipantManager()
    							    	.searchParticipant( activityID ),
    								resultSet.getBoolean( "canceled" ),
    								new OrganizationEvaluationManager()
    							  		.searchOrganizationEvaluation( activityID ),
    							  	new AdviserEvaluationManager()
    									.searchAdviserEvaluation( activityID ),
    								new BeneficiaryEvaluationManager()
    									.searchBeneficiaryEvaluation( activityID ) );
    			
    			activityList.add( currentActivity );
    		}
    		
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	finally {
	    	if ( getConnection() != null ) {
	    		disconnect();
	    	}
    	}
    	
    	return activityList;
    }
    
    
    
    /**
     *  Update Activity
     *  	- updates an activity in the volunteer activity table
     *  
     *  Parameters
     *		- VolunteerActivity original: the original version of
     *				the activity
     *		- VolunteerActivity changed: the changed version of
     *				the activity
     *	
     *	Returns
     *		- String "Original activity does not exist.": the activity is
     *				not found in the database
     *		- String "Lacking Requirements": requirements
     *				were not fulfilled
     *		- String "Success": activity is successfully updated
     * 		- String "Activity": updating failed for the activity table
     * 		- String "Activity_Organization": updating failed for the
     * 				v_activityOrganization table
     * 		- String "Beneficiary": updating failed for the beneficiary
     * 				table ( new beneficiaries )
     * 		- String "Activity_Beneficiary": updating failed for the
     * 				v_activityBeneficiary table
     * 		- String "Adviser": updating failed for the adviser table
     * 				( new advisers )
     * 		- String "Activity_Adviser": updating failed for the
     * 				v_activityAdviser table
     * 		- String "Participant": updating failed for the participant
     * 				table ( new participants )
     * 		- String "Activity_Participant": updating failed for the
     * 				v_activityParticipant table
     * 		- String "Remark": updating failed for the v_Remark table
     */
    public String updateActivity( VolunteerActivity original,
    							  VolunteerActivity changed ) {
        
        /* Declaration of Local Variables */
        int activityID = getActivityID( original );
        
        if ( activityID == 0 ) {
        	return "Original activity does not exist.";
        }
        
    	if ( lackingRequirement( changed ) ) {
    		return "Lacking Requirements";
    	}
    	
    	try {
            /* Declaration of Block Variables */
    		PreparedStatement preparedStatement; // Remove if preparedstatement is removed
    		// Statement statement; // TODO remove if preparedstatement works
    		// int canceled = 0; // TODO remove if preparedStatement works
    		ArrayList<Organization> organizationList;
            ArrayList<Organization> newOrganizationList;
            ArrayList<String> beneficiaryList;
            ArrayList<String> newBeneficiaryList;
            ArrayList<String> adviserList;
            ArrayList<String> newAdviserList;
            ArrayList<Participant> participantList;
            ArrayList<Participant> newParticipantList;
            ArrayList<String> remarkList;
            ArrayList<String> newRemarkList;
    		
    		/* Update activity data in `volunteerActivity` table */
    		connect();
    		
            /** Please remove this part (PreparedStatement) if it doesn't work. */
    		preparedStatement = getConnection()
    							.prepareStatement( "UPDATE `volunteerActivity`" +
    											   " SET `title` = ?," +
    											   " `activityType` = ?," +
    											   " `dateFiled` = ?," +
    											   " `dateApproved` = ?," +
    											   " `dateOfActivity` = ?," +
    											   " `dateDue` = ?," +
    											   " `canceled` = ?" );
    		
    		preparedStatement.setString( 1, changed.getTitle() );
    		preparedStatement.setString( 2, changed.getType() );
    		preparedStatement.setString( 3, changed.getDateFiled()
    										.toString() );
    		preparedStatement.setString( 4, changed.getDateApproved()
    										.toString() );
    		preparedStatement.setString( 5, changed.getDateOfActivity()
    										.toString() );
    		preparedStatement.setString( 6, changed.getDateDue()
    										.toString() );
    		preparedStatement.setBoolean( 7, changed.isCanceled() );
    		preparedStatement.executeUpdate();
    		//*/
    		
            /** Please remove this part (Statement) if the PreparedStatement works. *//*
            statement = getConnection().createStatement();
            
            if ( changed.isCanceled() ) {
            	canceled = 1;
            }
            
            statement.executeUpdate( "UPDATE `volunteerActivity` SET" +
    								 " `title` = \"" + changed.getTitle() +
    								 "\", `activityType` = \"" +
    								 changed.getType() + "\", `dateFiled` = \"" +
    								 changed.getDateFiled().toString() + "\"," +
    								 " `dateApproved` = \"" +
    								 changed.getDateFiled().toString() + "\"," +
    								 " `dateOfActivity` = \"" +
    								 changed.getDateFiled().toString() + "\"," +
    								 " `canceled` = " + canceled );
            //*/
    		
            disconnect();
            
            if ( getActivityID( changed ) != activityID ) {
            	return "Activity";
            }
            
            /* Update organization links in database */
            organizationList = original.getOrganizations();
            newOrganizationList = changed.getOrganizations();
            
            for ( Organization organization : organizationList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( Organization newOrganization : newOrganizationList ) {
            		if ( organization.getName()
            			 .equals( newOrganization.getName() ) ) {
            			
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int organizationID = new OrganizationManager()
                						 .getOrganizationID( organization );
                	
                	if ( organizationID != 0 ) {
                		/* Delete relation from `v_activityOrganization` table */
                		if ( !( new VolunteerActivityRelationsManager()
                			.deleteActivityOrganizationRelation( activityID,
                												 organizationID ) ) ) {
                			return "Activity_Organization";
                		}
                	}
            	}
            }
            
            for ( Organization newOrganization : newOrganizationList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( Organization organization : organizationList ) {
            		if ( newOrganization.getName()
            			 .equals( organization.getName() ) ) {
            			
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int organizationID = new OrganizationManager()
                						 .getOrganizationID( newOrganization );
                	
                	if ( organizationID != 0 ) {
                		/* Insert relation into `v_activityOrganization` table */
                		if ( !( new VolunteerActivityRelationsManager()
                			.insertActivityOrganizationRelation( activityID,
                												 organizationID ) ) ) {
                			return "Activity_Organization";
                		}
                	}
            	}
            }
            
            beneficiaryList = original.getBeneficiaries();
            newBeneficiaryList = changed.getBeneficiaries();
            
            for ( String beneficiary : beneficiaryList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( String newBeneficiary : newBeneficiaryList ) {
            		if ( beneficiary.equals( newBeneficiary ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int beneficiaryID = new BeneficiaryManager()
                						.getBeneficiaryID( beneficiary );
                	
                	if ( beneficiaryID != 0 ) {
                		/* Delete relation from `v_activityBeneficiary` table */
                		if ( !( new VolunteerActivityRelationsManager()
                			.deleteActivityBeneficiaryRelation( activityID,
                												beneficiaryID ) ) ) {
                			return "Activity_Beneficiary";
                		}
                	}
            	}
            }
            
            for ( String newBeneficiary : newBeneficiaryList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( String beneficiary : beneficiaryList ) {
            		if ( newBeneficiary.equals( beneficiary ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int beneficiaryID = new BeneficiaryManager()
                						.getBeneficiaryID( newBeneficiary );
                	
                	if ( beneficiaryID == 0 ) {
                		if ( !( new BeneficiaryManager()
                				.insertBeneficiary( newBeneficiary ) ) ) {
                			
                			return "Beneficiary";
                		}
                		
                		beneficiaryID = new BeneficiaryManager()
                						.getBeneficiaryID( newBeneficiary );
                	}
            		
            		/* Insert relation into `v_activityBeneficiary` table */
            		if ( !( new VolunteerActivityRelationsManager()
            			.insertActivityBeneficiaryRelation( activityID,
            												beneficiaryID ) ) ) {
            			return "Activity_Beneficiary";
            		}
            	}
            }
            
            adviserList = original.getAdvisers();
            newAdviserList = changed.getAdvisers();
            
            for ( String adviser : adviserList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( String newAdviser : newAdviserList ) {
            		if ( adviser.equals( newAdviser ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int adviserID = new AdviserManager().getAdviserID( adviser );
                	
                	if ( adviserID != 0 ) {
                		/* Delete relation from `v_activityAdviser` table */
                		if ( !( new VolunteerActivityRelationsManager()
                			.deleteActivityAdviserRelation( activityID,
                											adviserID ) ) ) {
                			return "Activity_Adviser";
                		}
                	}
            	}
            }
            
            for ( String newAdviser : newAdviserList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( String adviser : adviserList ) {
            		if ( newAdviser.equals( adviser ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int adviserID = new AdviserManager()
                					.getAdviserID( newAdviser );
                	
                	if ( adviserID == 0 ) {
                		if ( !( new AdviserManager()
                				.insertAdviser( newAdviser ) ) ) {
                			return "Adviser";
                		}
                		
                		adviserID = new AdviserManager()
                					.getAdviserID( newAdviser );
                	}
            		
            		/* Insert relation into `v_activityAdviser` table */
            		if ( !( new VolunteerActivityRelationsManager()
            				.insertActivityAdviserRelation( activityID,
            												adviserID ) ) ) {
            			return "Activity_Adviser";
            		}
            	}
            }
            
            participantList = original.getParticipants();
            newParticipantList = changed.getParticipants();
            
            for ( Participant participant : participantList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( Participant newParticipant : newParticipantList ) {
            		if ( participant.getName()
            			 .equals( newParticipant.getName() ) ) {
            			
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int participantID = new ParticipantManager()
                						.getParticipantID( participant );
                	
                	if ( participantID != 0 ) {
                		/* Delete relation from `v_activityParticipant` table */
                		if ( !( new VolunteerActivityRelationsManager()
                			.deleteActivityParticipantRelation( activityID,
                												participantID ) ) ) {
                			return "Activity_Participant";
                		}
                	}
            	}
            }
            
            for ( Participant newParticipant : newParticipantList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( Participant participant : participantList ) {
            		if ( newParticipant.getName()
            			 .equals( participant.getName() ) ) {
            			
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	
                    /* Declaration of Block Variables */
                	int participantID = new ParticipantManager()
                						.getParticipantID( newParticipant );
                	
                	if ( participantID == 0 ) {
                		if ( !( new ParticipantManager()
                				.insertParticipant( newParticipant ) ) ) {
                			
                			return "Participant";
                		}
                		
                		participantID = new ParticipantManager()
                						.getParticipantID( newParticipant );
                	}
            		
            		/* Insert relation into `v_activityParticipant` table */
            		if ( !( new VolunteerActivityRelationsManager()
            			.insertActivityParticipantRelation( activityID,
            												participantID ) ) ) {
            			return "Activity_Participant";
            		}
            	}
            }
            
            remarkList = original.getRemarks();
            newRemarkList = changed.getRemarks();
            
            for ( String remark : remarkList ) {
            	
                /* Declaration of Block Variables */
            	boolean found = false;
            	
            	for ( String newRemark : newRemarkList ) {
            		if ( remark.equals( newRemark ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	/* Delete remark from `v_remark` table */
                	if ( !( new RemarkManager().deleteRemark( activityID,
                											  remark,
                											  "Volunteer" ) ) ) {
                		return "Remark";
                	}
            	}
            }
            
            for ( String newRemark : newRemarkList ) {
            	boolean found = false;
            	
            	for ( String remark : remarkList ) {
            		if ( newRemark.equals( remark ) ) {
            			found = true;
            		}
            	}
            	
            	if ( !found ) {
                	/* Insert remark into `v_remark` table */
            		if ( !( new RemarkManager().insertRemark( activityID,
            												  newRemark,
            												  "Curriculum" ) ) ) {
            			return "Remark";
            		}
            	}
            }
            
            return "Success";
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return "Activity";
    }
    
    
    
    /**
     *  Delete Activity
     *  	- deletes an activity from the volunteer activity table
     *  
     *  Parameters
     *		- VolunteerActivity activity: the activity to be deleted
     *	
     *	Returns
     *		- boolean true: activity is successfully deleted
     *		- boolean false: deletion failed
     */
    public boolean deleteActivity( VolunteerActivity activity ) {
        
        /* Declaration of Local Variables */
        int activityID = getActivityID( activity );
        
        if ( activityID == 0 ) {
        	return false;
        }
        
    	try {
            /* Declaration of Block Variables */
    		PreparedStatement preparedStatement = null;
    		
    		if ( !new VolunteerActivityRelationsManager()
    			 .deleteActivityAdviserRelation( activityID, 0 ) ) {
    			
    			return false;
    		}
    		
    		if ( !new VolunteerActivityRelationsManager()
    			 .deleteActivityParticipantRelation( activityID, 0 ) ) {
			
    			return false;
    		}
    		
    		if ( !new VolunteerActivityRelationsManager()
    			 .deleteActivityBeneficiaryRelation( activityID, 0 ) ) {
			
    			return false;
    		}
    		
    		if ( !new VolunteerActivityRelationsManager()
    			 .deleteActivityOrganizationRelation( activityID, 0 ) ) {
			
    			return false;
    		}
    		
    		if ( new RemarkManager().deleteActivityRemark( activityID,
    													   "Volunteer" ) ) {
			
    			return false;
    		}
    		
    		connect();
            
    		preparedStatement = getConnection()
    							.prepareStatement( "DELETE FROM" +
    											   " `volunteerActivity`" +
    											   " WHERE `activityID`" +
    											   " = ?" );
    		
    		preparedStatement.setInt( 1, activityID );
    		preparedStatement.executeUpdate();
    		
    		disconnect();
    		
    		if ( getActivityID( activity ) == 0 ) {
    			return true;
    		}
    	}
    	catch ( Exception ex ) {
    		ex.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return false;
    }
    
    
    
    /**
     *  Get Activity ID
     *  	- finds the ID of the activity in the activity table
     *  
     *  Parameters
     *		- VolunteerActivity activity: the activity to be found
     *	
     *	Returns
     *		- non-zero int: activity ID found
     * 		- int 0: activity not found
     */
    public int getActivityID( VolunteerActivity activity ) {
    	try {
    		int activityID = 0;
    		int canceled = 0;
    		Statement statement;
    		ResultSet resultSet;
    		
    		connect();
    		
    		statement = getConnection().createStatement();
    		
    		if ( activity.isCanceled() ) {
    			canceled = 1;
    		}
    		
    		resultSet = statement.executeQuery( "SELECT `activityID` " +
    											"FROM `volunteerActivity` " +
    											"WHERE `title` = \"" +
    											activity.getTitle() + "\" AND " +
    											"`activityType` = \"" +
    											activity.getType() + "\" AND " +
    											"`dateFiled` = \"" +
    											activity.getDateFiled().toString() +
    											"\" AND `dateApproved` = \"" +
    											activity.getDateApproved().toString() +
    											"\" AND `dateOfActivity` = \"" +
    											activity.getDateOfActivity().toString() +
    											"\" AND `dateDue` = \"" +
    											activity.getDateDue().toString() +
    											"\" AND `canceled` = " + canceled );
    		
    		while ( resultSet.next() ) {
    			activityID = resultSet.getInt( "activityID" );
    		}
    		
    		resultSet.close();
    		disconnect();
    		return activityID;
    	}
    	catch ( Exception e ) {
    		e.printStackTrace();
    	}
    	
    	if ( getConnection() != null ) {
    		disconnect();
    	}
    	return 0;
    }
    
    
    
    /**
     *  Lacking Requirement
     *  	- checks if all requirements are filled
	 * 
     *  Parameters
     *		- VolunteerActivity activity: activity to be
     *				checked
     *	
     *	Returns
     *		- boolean true: activity is lacking requirements
     * 		- boolean false: activity is not lacking any
     * 				requirement
     */
    private boolean lackingRequirement( VolunteerActivity activity ) {
    	if ( activity.getTitle() == null || activity.getTitle().equals( "" ) ) {
    		return true;
    	}
    	
    	if ( activity.getType() == null || activity.getType().equals( "" ) ) {
    		return true;
    	}
    	
    	if ( activity.getDateFiled() == null ||
   			 activity.getDateFiled().toString().equals("1990-1-0") ) {
    		return true;
    	}
    	
    	if ( activity.getDateApproved() == null ||
      		 activity.getDateApproved().toString().equals("1990-1-0") ) {
    		return true;
    	}
    	
    	if ( activity.getDateOfActivity() == null ||
      		 activity.getDateOfActivity().toString().equals("1990-1-0") ) {
    		return true;
    	}
    	
    	if ( activity.getDateDue() == null ||
      		 activity.getDateDue().toString().equals("1990-1-0") ) {
    		return true;
    	}
    	
    	{
    		ArrayList<Organization> organizationList = activity.getOrganizations();
	    	if ( organizationList == null || organizationList.size() < 1 ) {
	    		return true;
	    	}
    	}
    	
    	{
    		ArrayList<String> beneficiaryList = activity.getBeneficiaries();
	    	if ( beneficiaryList == null || beneficiaryList.size() < 1 ) {
	    		return true;
	    	}
    	}
    	
    	{
    		ArrayList<String> adviserList = activity.getAdvisers();
	    	if ( adviserList == null || adviserList.size() < 1 ) {
	    		return true;
	    	}
    	}
    	
    	{
    		ArrayList<Participant> participantList = activity.getParticipants();
	    	if ( participantList == null || participantList.size() < 1) {
	    		return true;
	    	}
    	}
    	
    	return false;
    }
}
